package com.api.gbhg.daiyanlu.Dao;

import weaver.conn.RecordSet;

/**
 * 报表中心Dao层
 * Mr.dyl
 */
public class TheReportCenterDao {
    /**
     * 预算总额报表sql+分页
     * @param pageNum 当前页面
     * @param pageSize 显示数量
     * @return
     */
    public static RecordSet getStatement(Integer pageNum,Integer pageSize){
        RecordSet rs=new RecordSet();
        StringBuilder sql=new StringBuilder();
        sql.append("select j.nd,j.sbdw,jv.sbks,j.ze,j.syed \n" +
                "from uf_jingfei j INNER join uf_jingfei_version jv \n" +
                "on j.requestId = jv.requestId");
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sql.append(" order by jv.requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sql.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 预算执行情况报表sql+分页
     * @param pageNum 当前页面
     * @param pageSize 显示数量
     * @return
     */
    public static RecordSet getPerformance(Integer pageNum,Integer pageSize){
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        sql.append("select j.nd,j.sbdw,jv.sbks,j.ze,j.syed from uf_jingfei j inner join uf_jingfei_version jv on j.requestId=jv.requestId");
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sql.append(" order by jv.requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sql.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 预算编报二上二下项目统计报表sql+分页
     * @param pageNum 当前页面
     * @param pageSize 显示数量
     * @return
     */
    public static RecordSet getCompilation(Integer pageNum,Integer pageSize){
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        sql.append("select j.nd,j.sbdw,jv.sbks,j.xmmc from uf_jingfei j inner join uf_jingfei_version jv on j.requestId=jv.requestId");
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sql.append(" order by jv.requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sql.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 费用查询统计报表sql+分页
     * @param pageNum 当前页面
     * @param pageSize 显示数量
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet getQuery(Integer pageNum,Integer pageSize,String dw,String bm){
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")){
            sql.append("select j1.bxlcmc,j1.lcbh,j1.bxr,j1.bxrbm,j1.sqrq,j1.bxje,s.supsubcomid \n" +
                    "from uf_jingfei j join uf_jingfei_dt1 j1 \n" +
                    "on j.id=j1.mainid join HrmSubCompany s\n" +
                    "on j1.bxrbm = s.id join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where j.lcbj=0 and jv.sbks="+bm+"");
        }else{
            sql.append("select j1.bxlcmc,j1.lcbh,j1.bxr,j1.bxrbm,j1.sqrq,j1.bxje,s.supsubcomid \n" +
                    "from uf_jingfei j join uf_jingfei_dt1 j1 \n" +
                    "on j.id=j1.mainid join HrmSubCompany s\n" +
                    "on j1.bxrbm = s.id join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where j.lcbj=0 and j.sbdw="+dw+" and jv.sbks="+bm+" ");
        }
        pageNum=(pageNum-1)*pageSize;
        if("sqlserver".equals(rs.getDBType())){
            sql.append(" order by requestId offset "+pageNum+" rows fetch next "+pageSize+" rows only");
        }else if("mysql".equals(rs.getDBType())){
            sql.append(" LIMIT "+pageNum+","+pageSize+"");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 费用查询统计报表总条数
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public int getQueryCount(String dw,String bm){
        RecordSet rs = new RecordSet();
        String sql ;
        if (dw.equals("2")){
            sql = "select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv \n" +
                    "on j.requestId = jv.requestId where j.lcbj=0 and jv.sbks="+bm+"";
        }else{
            sql = "select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv \n" +
                    "ton j.requestId = jv.requestId where j.lcbj=0 and j.sbdw="+dw+" and jv.sbks="+bm+"  ";
        }
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("num");
    }

    /**
     * 根据Id查询所有部门
     * @param id  部门Id
     * @return
     */
    public static String getSectionId(Integer id){
        RecordSet rs = new RecordSet();
        String sql = "select id,departmentname from HrmDepartment where id="+id+"";
        rs.executeQuery(sql);
        return rs.getString("id");
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet getClassified(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")){
            sql.append("select l.lmc,sum(j.ze) je from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l on l.ldm=k.sjl \n" +
                    "where l.ldm in(501,502,503,504,505,506,507,508,509,510,511,512,513,514,599) group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm in(501,502,503,504,505,506,507,508,509,510,511,512,513,514,599) and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([501]机关工资福利支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet getWageWelfare(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je1 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=501 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je1 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=501 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([502]机关商品和服务支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet GoodsServices(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")){
            sql.append("select l.lmc,sum(j.ze) je2 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=502 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je2 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=502 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([503]机关资本性支出（一）)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet capitalizationOne(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je3 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=503 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je3 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=503 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([504]机关资本性支出（二）)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet capitalizationTwo(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je4 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=504 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je4 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=504 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([505]对事业单位经常性补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet PublicOften(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je5 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=505 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je5 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=505 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([506]对事业单位资本性补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet PublicCapital(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je6 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=506 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je6 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=506 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([507]对企业补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet SubsidiesToEnterprises(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je7 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=507 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je7 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=507 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([508]对企业资本性支出)
     * @param dw 单位
     * @param bm 部门c
     * @return
     */
    public static RecordSet apitalToBusiness(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je8 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=508 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je8 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=508 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([509]对个人和家庭的补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet individualsFamilies(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je9 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=509 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je9 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=509 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([510]对社会保障基金补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet socialInsurance(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je10 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=510 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je10 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=510 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([511]债务利息及费用支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet debtInteres(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je11 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=511 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je11 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=511 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([512]债务还本支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet repaymentPrincipal(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je12 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=512 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je12 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=512 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([513]转移性支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet transfer(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je13 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=513 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je13 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=513 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([514]预备费及预留)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet ReservesReservations(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je14 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=514 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je14 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=514 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表sql([599]其他支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet otherExpenses(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je15 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=599 group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je15 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm=599 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc ");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计政府经济支出统计报表总金额sql
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet getClassifiedSum(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select SUM(je) ze from (select l.lmc,sum(j.ze) je from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm in(501,502,503,504,505,506,507,508,509,510,511,512,513,514,599) group by l.ldm,l.lmc) tab");
        }else{
            sql.append("select SUM(je) ze from (select l.lmc,sum(j.ze) je from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.zfjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm in(501,502,503,504,505,506,507,508,509,510,511,512,513,514,599) and j.sbdw="+dw+" and jv.sbks="+bm+" group by l.ldm,l.lmc) tab");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }



    /**
     * 经济支出分类统计部门经济支出统计报表sql([301]工资福利支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet getClassifiedDepartment(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je1 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =301  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je1 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =301 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([302]商品和服务支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet GoodsAndServices(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je2 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =302  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je2 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =302 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([303]对个人和家庭的补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet SubsidiesIndividuals(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je3 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =303  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je3 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =303 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([307]债务利息及费用支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet SubsidizedDebt(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je7 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =307  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je7 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =307 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([309]资本性支出（基本建设）)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet  CapitalExpenditure(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je9 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =309  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je9 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =309 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([310]资本性支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet CapitalExpenditures(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je10 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =310  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je10 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =310 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([311]对企业补助（基本建设）)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet  enterprises(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je11 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =311  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je11 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =311 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([312]对企业补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet  toEnterprises(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je12 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =312  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je12 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =312 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([313]对社会保障基金补助)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet socialSecurity(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je13 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =313  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je13 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =313 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 经济支出分类统计部门经济支出统计报表sql([399]其他支出)
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet expenses(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2")&&bm.equals("1")){
            sql.append("select l.lmc,sum(j.ze) je99 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =399  group by l.ldm,l.lmc");
        }else{
            sql.append("select l.lmc,sum(j.ze) je99 from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm =399 and j.sbdw="+dw+" and jv.sbks="+bm+"  group by l.ldm,l.lmc");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 部门支出分类分类政府经济支出统计报表总金额sql
     * @param dw 单位
     * @param bm 部门
     * @return
     */
    public static RecordSet getSectoralSum(String dw,String bm){
        RecordSet rs=new RecordSet();
        StringBuilder sql = new StringBuilder();
        if (dw.equals("2") && bm.equals("1")){
            sql.append("select SUM(je) ze from (select l.lmc,sum(j.ze) je from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm in(301,302,303,307,309,310,311,312,313,399) group by l.ldm,l.lmc) tab");
        }else{
            sql.append("select SUM(je) ze from (select l.lmc,sum(j.ze) je from uf_jingfei j inner join uf_jjzcflkmk k \n" +
                    "on j.bmzcjjfl=k.kdm inner join uf_jjzcflkml l \n" +
                    "on l.ldm=k.sjl inner join uf_jingfei_version jv\n" +
                    "on j.requestId=jv.requestId\n" +
                    "where l.ldm in(301,302,303,307,309,310,311,312,313,399) and j.sbdw="+dw+" and jv.sbks="+bm+" group by l.ldm,l.lmc) tab");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }
    /**
     * 报表中心各报表总条数
     * @return
     */
    public int getStatementCount(){
        RecordSet rs=new RecordSet();
        String sql="select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv on j.requestId = jv.requestId";
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("num");
    }
}
